package com.iframework.tools;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DBHelp_JDBC_MySQL {
	private static String strDB_URL;
	private static String strDriver;
	private static String username;
	private static String password;
	private static boolean isThread = false;

	public static boolean getThread() {
		return isThread;
	}

	public static void setThread(boolean isThread) {
		DBHelp_JDBC_MySQL.isThread = isThread;
	}

	/**
	 * @return _strDB_URL
	 */
	public static String get_strDB_URL() {
		if (strDB_URL == null || strDB_URL == "") {
			strDB_URL="jdbc:mysql://localhost:3306/readbook?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull";
			
		}
		return strDB_URL;
	}

	/**
	 * @param _strDB_URL
	 *            要设置的 _strDB_URL
	 */
	public static void set_strDB_URL(String _strDB_URL) {
		strDB_URL = _strDB_URL;
	}

	/**
	 * @return _strDrvier
	 */
	public static String get_strDriver() {
		if (strDriver == null || strDriver == "") {
			strDriver = "com.mysql.jdbc.Driver";
		}
		return strDriver;
	}

	/**
	 * @param _strDrvier
	 *            要设置的 _strDrvier
	 */
	public static void set_strDriver(String _strDriver) {
		strDriver = _strDriver;
	}

	/**
	 * @return _username
	 */
	public static String get_username() {
		if (username == null || username == "") {
			username = "root";
		}
		return username;
	}

	/**
	 * @param _username
	 *            要设置的 _username
	 */
	public static void set_username(String _username) {
		username = _username;
	}

	/**
	 * @return _password
	 */
	public static String get_password() {
		if (password == null || password == "") {
			password = "Aj88997755";
		}
		return password;
	}

	/**
	 * @param _password
	 *            要设置的 _password
	 */
	public static void set_password(String _password) {
		password = _password;
	}

	public static Connection conn;
	public static PreparedStatement pstmt;
	public static ResultSet rs;
	public static String[] result = null;
	public DBHelp_JDBC_MySQL(boolean isThread) {
		try {
			// Class.forName(bundle.getString("db.classname"));// 加载Mysql数据驱动
			// con = DriverManager.getConnection(bundle.getString("db.url"),
			// bundle.getString("db.username"),
			// bundle.getString("db.password"));
			// 创建数据连接
			setThread(isThread);
			Class.forName(get_strDriver());
			conn = DriverManager.getConnection(get_strDB_URL(), get_username(), get_password());// 创建数据连接
		} catch (Exception e) {
			BaseTools.log("数据库连接失败" + e.getMessage());
		}

	}

	// 数据库关闭操作
	public void close() {
		try {
			this.conn.close();
			this.pstmt.close();
		} catch (SQLException e) {
			BaseTools.log("数据库关闭失败" + e.getMessage());

		}
	}

	/**
	 * 获取数据库连接的函数
	 */
	private static Connection getConnection() {
		try {
			Class.forName(get_strDriver());// 加载Mysql数据驱动
			conn = DriverManager.getConnection(get_strDB_URL(), get_username(), get_password());// 创建数据连接
		} catch (Exception e) {
			BaseTools.log("数据库连接失败" + e.getMessage());
		}
		return conn; // 返回所建立的数据库连接
	}

	/**
	 * 用于注入参数
	 * 
	 * @param ps
	 * @param data
	 * @throws SQLException
	 */
	private static void excutePs(String sql, int length, String[] data) throws SQLException {
		// 预处理SQL 防止注入
		pstmt = conn.prepareStatement(sql);
		pstmt.clearParameters();
		// 注入参数
		for (int i = 0; i < length; i++) {
			if (data[i] == null) {
				pstmt.setString(i + 1, "");
			} else if (data[i].toLowerCase() == "true") {
				pstmt.setByte(i + 1, Byte.valueOf("1"));
			} else if (data[i].toLowerCase() == "false") {
				pstmt.setByte(i + 1, Byte.valueOf("0"));
			} else {
				pstmt.setString(i + 1, data[i].toString());
			}
		}
	}

	/**
	 * * 创建表
	 * 
	 * @param tabName
	 *            表名称
	 * @param tab_fields
	 *            表字段
	 */
	public static void createTable(String tabName, String[] tab_fields) {
		if (conn == null)
			conn = getConnection(); // 首先要获取连接，即连接到数据库
		try {
			String sql = "create table " + tabName + "(id int auto_increment primary key not null";

			if (tab_fields != null && tab_fields.length > 0) {
				sql += ",";
				int length = tab_fields.length;
				for (int i = 0; i < length; i++) {
					// 添加字段
					sql += tab_fields[i].trim() + " varchar(50)";
					// 防止最后一个,
					if (i < length - 1) {
						sql += ",";
					}
				}
			}
			// 拼凑完 建表语句 设置默认字符集
			sql += ")DEFAULT CHARSET=utf8;";
			// System.out.println("建表语句是："+sql);
			pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate(sql);
			if (!isThread) {
				pstmt.close();
				conn.close(); // 关闭数据库连接
			}

		} catch (SQLException e) {
			BaseTools.log("建表失败" + e.getMessage());
		}
	}

	/**
	 * 添加数据
	 * 
	 * @param tabName
	 *            表名
	 * @param fields
	 *            参数字段
	 * @param data
	 *            参数字段数据
	 */
	public static void insert(String tabName, String[] fields, String[] data,String TipName) {
		//String strTip = BaseTools.getPrintTitle(tabName) + "新增" + TipName;
		String strTip="【新增】*****"+(tabName=="tbl_MainRanking"?"榜单":(tabName=="tbl_book"?"书籍":(tabName=="tbl_booksource"?"来源":"章节")))+"*****\n\t"+TipName;
		if (conn == null)
			conn = getConnection(); // 首先要获取连接，即连接到数据库
		try {
			if (conn.isClosed())
				conn = getConnection();
			String sql = "insert into " + tabName + "(";
			int length = fields.length;
			for (int i = 0; i < length; i++) {
				sql += fields[i];
				// 防止最后一个,
				if (i < length - 1) {
					sql += ",";
				}
			}
			sql += ") values (";
			for (int i = 0; i < length; i++) {
				sql += "?";
				// 防止最后一个,
				if (i < length - 1) {
					sql += ",";
				}
			}
			sql += ");";
			// System.out.println("添加数据的sql:"+sql+"\n");
			// System.out.println("sql参数:"+data);
			// 预处理SQL 防止注入
			excutePs(sql, length, data);
			// 执行
			pstmt.executeUpdate();
			// 关闭流
			if (!isThread) {
				pstmt.close();
				conn.close(); // 关闭数据库连接
			}
			BaseTools.log(strTip + "成功！");
		} catch (SQLException e) {
			// BaseTools.log("添加数据失败" + e.getMessage());
			BaseTools.log(strTip + "失败！！！");
		}
	}

	/**
	 * 更新数据
	 * 
	 * @param tabName
	 *            表名
	 * @param u_fields
	 *            更新字段
	 * @param u_data
	 *            更新字段数据
	 * @param fields
	 *            参数字段
	 * @param data
	 *            参数字段数据
	 */
	public static void update(String tabName, String[] u_fields, String[] u_data, String[] fields, String[] data,String TipName) {
		//String strTip = BaseTools.getPrintTitle(tabName) + "更新" + TipName;
		String strTip="【更新】*****"+(tabName=="tbl_MainRanking"?"榜单":(tabName=="tbl_book"?"书籍":(tabName=="tbl_booksource"?"来源":"章节")))+"*****\n\t"+ TipName;
		
		if (conn == null)
			conn = getConnection(); // 首先要获取连接，即连接到数据库
		try {
			if (conn.isClosed())
				conn = getConnection();
			String sql = "update " + tabName + " set ";
			int u_length = u_fields.length;
			for (int i = 0; i < u_length; i++) {
				sql += u_fields[i] + " = ? ";
				// 防止最后一个,
				if (i < u_length - 1) {
					sql += " , ";
				}
			}
			sql += " where ";
			int length = fields.length;
			for (int i = 0; i < length; i++) {
				sql += fields[i] + " = ? ";
				// 防止最后一个,
				if (i < length - 1) {
					sql += " and ";
				}
			}
			sql += ";";

			// System.out.println("更新数据的sql:"+sql+"\n");
			// 预处理SQL 防止注入
			excutePs(sql, length + u_length, BaseTools.concat(u_data, data));
			// 执行
			pstmt.executeUpdate();

			if (!isThread) {
				pstmt.close();// 关闭流
				conn.close(); // 关闭数据库连接
			}
			BaseTools.log(strTip + "成功！");
		} catch (SQLException e) {
			// BaseTools.log("更新数据失败" + e.getMessage());
			BaseTools.log(strTip + "失败！！！");
		}
	}

	/**
	 * 查询表 【查询结果的顺序要和数据库字段的顺序一致】
	 * 
	 * @param tabName
	 *            表名
	 * @param fields
	 *            参数字段
	 * @param data
	 *            参数字段数据
	 * @param tab_fields
	 *            数据库的字段
	 */
	public static String[] query(String tabName, String[] fields, String[] data, String[] tab_fields) {
		if (conn == null)
			conn = getConnection(); // 首先要获取连接，即连接到数据库
		
		try {
			if (conn.isClosed())
				conn = getConnection();
			String sql = "select * from  " + tabName + " where ";
			int length = fields.length;
			for (int i = 0; i < length; i++) {
				sql += fields[i] + " = ? ";
				// 防止最后一个,
				if (i < length - 1) {
					sql += " and ";
				}
			}
			sql += ";";
			// System.out.println("查询sql:"+sql);
			// 预处理SQL 防止注入
			excutePs(sql, length, data);
			// 查询结果集
			rs = pstmt.executeQuery();
			// 存放结果集
			result = new String[tab_fields.length];
			if (rs != null) {
				while (rs.next()) {
					for (int i = 0; i < tab_fields.length; i++) {
						try {
							result[i] = rs.getString(tab_fields[i]);
						} catch (Exception ee) {
							result[i] = String.valueOf(rs.getInt(tab_fields[i]));
						}
					}
				}
			}
			// 关闭流

			if (!isThread) {
				rs.close();
				pstmt.close();
				conn.close(); // 关闭数据库连接
			}
		} catch (SQLException e) {
			result = null;
			BaseTools.log("查询失败" + e.getMessage());
		}
		return result;
	}

	/**
	 * 获取某张表总数
	 * 
	 * @param tabName
	 * @return
	 */
	public static Integer getCount(String tabName) {
		int count = 0;
		if (conn == null)
			conn = getConnection(); // 首先要获取连接，即连接到数据库
		try {
			if (conn.isClosed())
				conn = getConnection();
			String sql = "select count(*) from  " + tabName + " ;";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}

			if (!isThread) {
				rs.close();
				pstmt.close();
				conn.close(); // 关闭数据库连接
			}
		} catch (SQLException e) {
			BaseTools.log("获取总数失败" + e.getMessage());
		}
		return count;
	}

	/**
	 * 后台分页显示
	 * 
	 * @param tabName
	 * @param pageNo
	 * @param pageSize
	 * @param tab_fields
	 * @return
	 */
	public static List<String[]> queryForPage(String tabName, int pageNo, int pageSize, String[] tab_fields) {
		if (conn == null)
			conn = getConnection(); // 首先要获取连接，即连接到数据库
		List<String[]> list = new ArrayList<String[]>();
		try {
			String sql = "select * from  " + tabName + " LIMIT ?,? ; ";
			// System.out.println("查询sql:"+sql);
			// 预处理SQL 防止注入
			pstmt = conn.prepareStatement(sql);
			// 注入参数
			pstmt.setInt(1, pageNo);
			pstmt.setInt(2, pageSize);

			// 查询结果集
			rs = pstmt.executeQuery();
			// 存放结果集
			while (rs.next()) {
				result = new String[tab_fields.length];
				for (int i = 0; i < tab_fields.length; i++) {
					result[i] = rs.getString(tab_fields[i]);
				}
				list.add(result);
			}
			if (!isThread) {
				rs.close();// 关闭流
				pstmt.close();
				conn.close(); // 关闭数据库连接
			}
		} catch (SQLException e) {
			BaseTools.log("查询失败" + e.getMessage());
		}
		return list;
	}

	/**
	 * 清空表数据
	 * 
	 * @param tabName
	 *            表名称
	 */
	public static void delete(String tabName) {
		if (conn == null)
			conn = getConnection(); // 首先要获取连接，即连接到数据库

		try {
			String sql = "delete from  " + tabName + ";";
			// System.out.println("删除数据的sql:"+sql);
			// 预处理SQL 防止注入
			pstmt = conn.prepareStatement(sql);
			// 执行
			pstmt.executeUpdate();
			// 关闭流
			if (!isThread) {
				pstmt.close();
				conn.close(); // 关闭数据库连接
			}
		} catch (SQLException e) {
			BaseTools.log("删除数据失败" + e.getMessage());
		}
	}

	/**
	 * 判断表是否存在
	 * 
	 * @param tabName
	 * @return
	 */
	public static boolean exitTable(String tabName) {

		boolean flag = false;
		if (conn == null)
			conn = getConnection(); // 首先要获取连接，即连接到数据库
		try {
			String sql = "select * from  " + tabName + ";";
			// 预处理SQL 防止注入
			pstmt = conn.prepareStatement(sql);
			// 执行
			flag = pstmt.execute();
			// 关闭流
			if (!isThread) {
				pstmt.close();
				conn.close(); // 关闭数据库连接
			}
		} catch (SQLException e) {
			BaseTools.log("查询表是否存在失败" + e.getMessage());
		}
		return flag;
	}

	/**
	 * 删除数据表 如果执行成功则返回false
	 * 
	 * @param tabName
	 * @return
	 */
	public static boolean dropTable(String tabName) {
		boolean flag = true;
		conn = getConnection(); // 首先要获取连接，即连接到数据库
		try {
			String sql = "drop table  " + tabName + ";";
			// 预处理SQL 防止注入
			pstmt = conn.prepareStatement(sql);
			// 执行
			flag = pstmt.execute();
			// 关闭流
			if (!isThread) {
				pstmt.close();
				conn.close(); // 关闭数据库连接
			}
		} catch (SQLException e) {
			BaseTools.log("删除数据失败" + e.getMessage());
		}
		return flag;
	}

	/**
	 * 测试方法
	 * 
	 * @param args
	 */
	// 建表===========================================
	// 表名
	// String tabName = "mytable";
	// 表字段
	// String[] tab_fields = {"name","password","sex","age"};
	// 创建表
	// createTable(tabName, tab_fields);

	// 添加===========================================
	// 模拟数据
	// String[] data1 = {"jack","123456","男","25"};
	// String[] data2 = {"tom","456789","女","20"};
	// String[] data3 = {"mark","aaa","哈哈","21"};
	// 插入数据
	// insert(tabName, tab_fields, data1);
	// insert(tabName, tab_fields, data2);
	// insert(tabName, tab_fields, data3);

	// 查询=============================================
	// String[] q_fileds ={"name","sex"};
	// String[] data4 = {"jack","男"};
	//
	// String[] result = query(tabName, q_fileds, data4, tab_fields);
	// for (String string : result) {
	// System.out.println("结果：\t"+string);
	// }

	// 删除 清空=============================================
	// delete(tabName);
	// 是否存在
	// System.out.println(exitTable("mytable"));
	// 删除表
	// System.out.println(dropTable("mytable"));

	// 数据库的配置文件 db.properties=============================================
	// db.username=root
	// db.password=root
	// db.classname=com.mysql.jdbc.Driver
	// db.url =
	// jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
}
